/**********************************************************************
***********************************************************************
***********************************************************************
Symptom 
***********************************************************************
***********************************************************************
**********************************************************************/

/**********************************************************************
Add Symptom
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddSymptom') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddSymptom
GO

create procedure AddSymptom
	@name nvarchar(50),
	@pinyin nvarchar(50),
	@type int,
	@milddescription nvarchar(50),
	@moderatedescription nvarchar(50),
	@severedescription nvarchar(50),
	@intro nvarchar(4000),
	@desp nvarchar(max),
	@uploaddate datetime,
	@classPatientId int,
	@PatientCount int
as
   insert into [symptom]
   (
		symptomname,
		firstsymbolofname,
		symptomtypeid,
		milddescription,
		moderatedescription,
		severedescription,
		Introduction,
		description,      
		uploaddate,
		classPatientId,
		PatientCount
   )values(
		@name,
		@pinyin,
		@type,
		@mildDescription,
		@moderateDescription,
		@severeDescription,
		@intro,
		@desp,
		@uploaddate,
		@classPatientId,
		@PatientCount
   )   

GO

/**********************************************************************
UpdateSymptom
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateSymptom') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateSymptom

GO

create procedure UpdateSymptom
	@id int,
	@name nvarchar(50),
	@pinyin nvarchar(50),
	@type int,
	@milddescription nvarchar(50),
	@moderatedescription nvarchar(50),
	@severedescription nvarchar(50),
	@intro nvarchar(4000),
	@desp nvarchar(max),
	@updatetime datetime,
	@classPatientId int,
    @ClassPatientName nvarchar(50)
as
	UPDATE [Symptom]
	SET [symptomname] = @name
		,[Introduction] = @intro
		,[description] = @desp
		,[symptomtypeid] = @type
		,[milddescription] = @mildDescription
		,[moderatedescription] = @moderateDescription
		,[severedescription] = @severeDescription
		,[firstsymbolofname] = @pinyin
		,[uploaddate] = @updatetime
		,[classPatientId] = @classPatientId
        ,[ClassPatientName] = @ClassPatientName
	WHERE [id]=@id
   
GO


/**********************************************************************
UpdateSymptomPatientCount
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateSymptomPatientCount') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateSymptomPatientCount

GO

create procedure UpdateSymptomPatientCount
	@id int,
	@Num int
as
	UPDATE 
		[Symptom]
	SET 
		[PatientCount] = [PatientCount] + @Num
	WHERE 
		[id]=@id
   
GO

/**********************************************************************
  GetSymptom
**********************************************************************/	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetSymptom') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetSymptom
GO

create procedure GetSymptom
	@id int
as
SELECT  
	'Id' = [id],
	'Name' = [symptomname],
	'Pinyin' = [firstsymbolofname],
	'Type' = [symptomtypeid],
	'MildDescription' = [milddescription],
	'ModerateDescription' = [moderatedescription],
	'SevereDescription' = [severedescription],
	'Intro' = [Introduction],
	'Desp' = [description],
	'UpdateTime' = [uploaddate],
	'PatientId' = [classPatientId],
    'PatientName' = [ClassPatientName],
	[PatientCount]
FROM 
	[symptom]
WHERE 
	[id]=@id

GO

/**********************************************************************
  DelSymptom
**********************************************************************/	 
if exists 
	(
	select * from dbo.sysobjects 
	where id = object_id(N'DelSymptom') 
	and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure DelSymptom
GO

create procedure DelSymptom
	@condstr nvarchar(512)
as
	DECLARE @strSQL VARCHAR(600)
	
	set @strSQL = '
		delete from [symptom] where ' 
		+ @condstr
	
	exec(@strSQL)
GO


--region [dbo].[GetSymptomList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomList]
GO

CREATE PROCEDURE [dbo].[GetSymptomList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(id) 
FROM [Symptom]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *,
	''PatientAvatar'' = dbo.GetUserAvatar(tblTmp.PatientId)
FROM
	( SELECT 
	[Id],
	''Name'' = [symptomname],
	''Intro'' = [Introduction],
	''Type'' = [symptomtypeid],
	''Pinyin'' = [firstsymbolofname],
	''UpdateTime'' = [uploaddate],
	''PatientId'' = [ClassPatientId],
	''PatientName'' = [ClassPatientName],
	[PatientCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
	FROM 
		[Symptom] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSymptomListByDisease]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomListByDisease]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomListByDisease]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomListByDisease]
GO

CREATE PROCEDURE [dbo].[GetSymptomListByDisease]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Symptom].id) 
FROM
	[Symptom], [DiseaseSymptom]
WHERE
	[Symptom].[Id] = [DiseaseSymptom].[SymptomId] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *,
	''PatientAvatar'' = dbo.GetUserAvatar(tblTmp.PatientId)
FROM
	( SELECT 
	''Id'' = [Symptom].[id],
	''Name'' = [symptomname],
	''Intro'' = [Symptom].[Introduction],
	''Type'' = [symptomtypeid],
	''Pinyin'' = [Symptom].[firstsymbolofname],
	''UpdateTime'' = [Symptom].[uploaddate],
	''PatientId'' = [Symptom].[ClassPatientId],
	''PatientName'' = [Symptom].[ClassPatientName],
	[Symptom].[PatientCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
	FROM
		[Symptom], [DiseaseSymptom]
	WHERE
		[Symptom].[Id] = [DiseaseSymptom].[SymptomId] 	
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSymptomListWithPatientInfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomListWithPatientInfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomListWithPatientInfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomListWithPatientInfo]
GO

CREATE PROCEDURE [dbo].[GetSymptomListWithPatientInfo]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Symptom].[id]) 
FROM
	[Symptom], [UserInfo]
WHERE
	[Symptom].[ClassPatientId] = [UserInfo].[Id]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	''Id'' = [Symptom].[id],
	''Name'' = [symptomname],
	''Intro'' = [Symptom].[Introduction],
	''Type'' = [symptomtypeid],
	''Pinyin'' = [Symptom].[firstsymbolofname],
	''UpdateTime'' = [Symptom].[uploaddate],
	''PatientId'' = [ClassPatientId],
	''PatientName'' = [ClassPatientName],
	''PatientAvatar'' = [UserInfo].[Avatar],
	[Symptom].[PatientCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
	FROM
		[Symptom], [UserInfo]
	WHERE
		[Symptom].[ClassPatientId] = [UserInfo].[Id] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSymptomListByDiseaseWithPatientInfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomListByDiseaseWithPatientInfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomListByDiseaseWithPatientInfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomListByDiseaseWithPatientInfo]
GO

CREATE PROCEDURE [dbo].[GetSymptomListByDiseaseWithPatientInfo]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Symptom].id) 
FROM
	[Symptom], [DiseaseSymptom], [UserInfo]
WHERE
	[Symptom].[ClassPatientId] = [UserInfo].[Id] AND
	[Symptom].[Id] = [DiseaseSymptom].[SymptomId] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	''Id'' = [Symptom].[id],
	''Name'' = [symptomname],
	''Intro'' = [Symptom].[Introduction],
	''Type'' = [symptomtypeid],
	''Pinyin'' = [Symptom].[firstsymbolofname],
	''UpdateTime'' = [Symptom].[uploaddate],
	''PatientId'' = [Symptom].[ClassPatientId],
	''PatientName'' = [Symptom].[ClassPatientName],
	''PatientAvatar'' = [UserInfo].[Avatar],
	[Symptom].[PatientCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
	FROM
		[Symptom], [DiseaseSymptom]
	WHERE
		[Symptom].[Id] = [DiseaseSymptom].[SymptomId] 	
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSymptomListWithDiseaseInfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomListWithDiseaseInfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomListWithDiseaseInfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomListWithDiseaseInfo]
GO

CREATE PROCEDURE [dbo].[GetSymptomListWithDiseaseInfo]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Symptom].[Id]) 
FROM
	[Symptom], [disease], [UserInfo]
WHERE
	[Symptom].[ClassDiseaseId] = [Disease].[Id] AND
	[disease].[ClassPatientId] = [UserInfo].[Id]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	''Id'' = [Symptom].[id],
	''Name'' = [symptomname],
	''Intro'' = [Symptom].[Introduction],
	''Type'' = [symptomtypeid],
	''Pinyin'' = [Symptom].[firstsymbolofname],
	''UpdateTime'' = [Symptom].[uploaddate],
	''DiseaseId'' = [Symptom].[ClassDiseaseId],
	''DiseaseName'' = [DiseaseName],
	''PatientId'' = [Symptom].[ClassPatientId],
	''PatientName'' = [Symptom].[ClassPatientName],
	[Symptom].[PatientCount]
FROM
	[Symptom], [disease]
WHERE
	[Symptom].[ClassDiseaseId] = [Disease].[Id] 
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [Symptom].[id] < 
	( 
	SELECT MIN([id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Symptom].[id] 
	FROM
		[Symptom], [disease]
	WHERE
		[Symptom].[ClassDiseaseId] = [Disease].[Id]
	' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' AND ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSymptomListByDiseaseWithDiseaseInfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AdvanceSearchSymptomListWithDiseaseInfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomListByDiseaseWithDiseaseInfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomListByDiseaseWithDiseaseInfo]
GO

CREATE PROCEDURE [dbo].[GetSymptomListByDiseaseWithDiseaseInfo]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Symptom].[Id]) 
FROM
	[Symptom], [DiseaseSymptom], [disease], [UserInfo]
WHERE
	[Symptom].[ClassDiseaseId] = [Disease].[Id] AND
	[disease].[ClassPatientId] = [UserInfo].[Id] AND
	[Symptom].[Id] = [DiseaseSymptom].[SymptomId] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	''Id'' = [Symptom].[id],
	''Name'' = [symptomname],
	''Intro'' = [Symptom].[Introduction],
	''Type'' = [symptomtypeid],
	''Pinyin'' = [Symptom].[firstsymbolofname],
	''UpdateTime'' = [Symptom].[uploaddate],
	''DiseaseId'' = [Symptom].[ClassDiseaseId],
	''DiseaseName'' = [DiseaseName],
	''PatientId'' = [ClassPatientId],
	''PatientName'' = [ClassPatientName],
	''PatientAvatar'' = [UserInfo].[Avatar],
	[Symptom].[PatientCount]
FROM
	[Symptom], [DiseaseSymptom], [disease], [UserInfo]
WHERE
	[Symptom].[ClassDiseaseId] = [Disease].[Id] AND
	[disease].[ClassPatientId] = [UserInfo].[Id] AND
	[Symptom].[Id] = [DiseaseSymptom].[SymptomId] 
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [Symptom].[id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Symptom].[id] 
	FROM
		[Symptom], [DiseaseSymptom], [disease], [UserInfo]
	WHERE
		[Symptom].[ClassDiseaseId] = [Disease].[Id] AND
		[disease].[ClassPatientId] = [UserInfo].[Id] AND
		[Symptom].[Id] = [DiseaseSymptom].[SymptomId] 
	' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' AND ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


/***********************************************************************
************************************************************************
SymptomType
************************************************************************
***********************************************************************/

/**********************************************************************
AddSymptomType
***********************************************************************/  
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddSymptomType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddSymptomType
GO


create procedure AddSymptomType
   @name nvarchar(50)
   ,@parentid  int
as
    insert into [symptomtype](
        [parentid]
        ,[symptomtypename]
    )values(
        @parentid,
        @name
    )
     
Go


/**********************************************************************
Delsymptomtype
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'Delsymptomtype') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelSymptomType
GO


create procedure [DelSymptomType]
   @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)

   set @strSQL = '
   delete from [symptomtype]
   where ' + @condstr

   exec(@strSQL)

GO


/**********************************************************************
UpdateSymptomType
***********************************************************************/  
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateSymptomType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateSymptomType
GO


create procedure UpdateSymptomType
    @id  int
   ,@name nvarchar(50)
   ,@parentId int
as
   update [symptomtype] set
      [symptomtypename] = @name
     ,[parentid] = @parentid
	where
      [id] = @id
GO

/**********************************************************************
GetSymptomType
***********************************************************************/
	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetSymptomType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetSymptomType
GO

create procedure GetSymptomType
   @id int
as
   select 
        'Id' = [id],
        'Name' = [symptomtypename],
        'ParentId' = [parentid]
   from 
        [symptomtype]
   where [id]=@id
   
GO

/**********************************************************************
GetSymptomTypeList
***********************************************************************/  
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetSymptomTypeList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetSymptomTypeList
GO


create procedure [GetSymptomTypeList]
   @condstr  nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = 'select 
           ''Id'' = [id]
          ,''Name'' = [symptomtypename]
          ,''ParentId'' = [parentid]
   from
		  [symptomtype]'
	
	 if @condstr <> ''
		 begin
		 	set @strSQL = @strSQL + ' where ' + @condstr
		 end
   
   exec(@strSQL)
GO


--region [dbo].[GetSymptomStatSeverity]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomStatSeverity]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomStatSeverity]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomStatSeverity]
GO

CREATE PROCEDURE [dbo].[GetSymptomStatSeverity]
	@SymptomId int
AS

SELECT 
	'PatientCount' = COUNT([PatientCurrentSymptom].[Id]),
	[PatientCurrentSymptom].[DegreeValue] 
FROM	
	[PatientCurrentSymptom]
WHERE 
	[PatientCurrentSymptom].[DegreeValue] > 0 AND
	[PatientCurrentSymptom].[SymptomId] = @SymptomId
Group by
	[PatientCurrentSymptom].[DegreeValue]
Order by
	[PatientCurrentSymptom].[DegreeValue] DESC

GO
--endregion


--region [dbo].[GetSymptomStatDisease]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomStatDisease]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomStatDisease]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomStatDisease]
GO

CREATE PROCEDURE [dbo].[GetSymptomStatDisease]
	@SymptomId int
AS

SELECT 
	'PatientCount' = COUNT([PatientRecord].[DiseaseId]),
	[PatientRecord].[DiseaseId],
	'DiseaseName' = dbo.GetDiseaseName([PatientRecord].[DiseaseId])
FROM	
	[PatientRecord], [PatientCurrentSymptom]
WHERE 
	[PatientCurrentSymptom].[DegreeValue] > 0 AND
	[PatientCurrentSymptom].[SymptomId] = @SymptomId AND
	[PatientRecord].[Id] = [PatientCurrentSymptom].[PRId]
Group by
	[PatientRecord].[DiseaseId]
Order by
	[PatientCount] DESC
GO
--endregion
